{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Defining Schema in SQLAlchemy ORM\r\n",
    "在 SQLAlchemy ORM 中定义模式"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# SQLAlchemy ORM\r\n",
    "\r\n",
    "SQLAlchemy ORM (对象关系映射器)是一种使用 Python 类定义表和表之间关系的方法。它还提供了一个使用面向对象代码而不是编写 SQL 来查询和操作数据库的系统。不像 SQLAlchemy Core，它主要围绕表、行和列; ORM 主要围绕对象和模型。\r\n",
    "\r\n",
    "ORM 是构建在 SQLAlchemy Core 之上的，因此到目前为止您所学到的一切仍然适用。\r\n",
    "\r\n",
    "正如您将看到的，ORM 允许您提高生产率，但是它也增加了查询的额外开销。然而，对于大多数应用程序来说，它的好处远远大于性能损失。\r\n",
    "\r\n",
    "注意: 在进入下一部分之前，使用以下命令从 sqlalchemy-tuts 数据库中删除所有表:\r\n",
    "\r\n",
    "`metadata.drop_all(engine)`"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Defining Models 定义模型\r\n",
    "\r\n",
    "A Model is a Python class which corresponds to the database table and its attributes represent the columns.\r\n",
    "\r\n",
    "Model 是一个 Python 类，它对应于数据库表，其属性表示列。\r\n",
    "\r\n",
    "For the class to be a valid model, it must do the following:\r\n",
    "\r\n",
    "要使该类成为一个有效的模型，它必须执行以下操作:\r\n",
    "\r\n",
    "1. Inherit from a declarative base class created by calling declarative_base() function. 从通过调用 declarative_base() 函数创建的声明性基类继承。\r\n",
    "2. define table name via __tablename__ attribute. 定义表名通过__tablename__属性\r\n",
    "3. define at-least one column which must be a part of the primary key. 至少定义一列，该列必须是主键的一部分\r\n",
    "\r\n",
    "The last two points are self-explanatory but the first one deserves a bit of explanation. \r\n",
    "最后两点是不言自明的，但第一点值得解释一下。\r\n",
    "\r\n",
    "The base class maintains a catalog of classes and tables. In other words, the declarative base class wraps the mapper and the MetaData. The mapper maps the subclass to the table and MetaData holds all the information about the database and the tables it contains. Just as in Core, in ORM we use create_all() and drop_all() methods of the MetaData object to create and drop tables.\r\n",
    "\r\n",
    "基类维护类和表的目录。换句话说，声明性基类包装(wraps)映射器(mapper)和元数据(MetaData)。映射器mapper将子类映射到表，元数据MetaData保存有关数据库及其包含的表的所有信息。正如在 Core 中一样，在 ORM 中，我们使用 MetaData 对象的 create_all()和 drop_all()方法来创建和删除表。\r\n",
    "\r\n",
    "The following listing defines a Post model which can be used to store blog posts.\r\n",
    "\r\n",
    "下面的清单定义了一个 Post 模型，可用于存储博客文章。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric\r\n",
    "from sqlalchemy.ext.declarative import declarative_base\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "Base = declarative_base()\r\n",
    "\r\n",
    "class Post(Base):\r\n",
    "    __tablename__ = 'posts'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    slug = Column(String(100), nullable=False)\r\n",
    "    content = Column(String(50), nullable=False)\r\n",
    "    published = Column(String(200), nullable=False, unique=True)    \r\n",
    "    created_on = Column(DateTime(), default=datetime.now)\r\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Let's step through the code line by line.\r\n",
    "\r\n",
    "让我们逐行过一遍上面的代码。\r\n",
    "\r\n",
    "1. In line 1-4, we import necessary classes and functions. 在第1-4行中，我们导入必要的类和函数\r\n",
    "2. In line 6, we create a base declarative class by calling the declarative_base() function. 在第6行中，我们通过调用declarative_base()函数创建了一个基本的声明类(基类).\r\n",
    "3. In line 10-16, we define columns as class attributes. 在第10-16行中，我们将列定义为类属性\r\n",
    "\r\n",
    "Notice that we are using the same Column class to define the columns as we did while using SQLAlchemy Core. The only difference is that now the first argument is type instead of the column name. Also, notice that the keyword arguments passed to the Column() works exactly the same in ORM and Core.\r\n",
    "\r\n",
    "请注意，我们使用了相同的 Column 类来定义列，就像我们在使用 SQLAlchemy Core 时所做的那样。唯一的区别是，现在第一个参数是 type 而不是列名。另外，请注意，传递给 Column()的关键字参数在 ORM 和 Core 中的工作方式完全相同。\r\n",
    "\r\n",
    "Since ORM is built upon Core, SQLAlchemy will use the model definition to create a Table object and associate it with the model by calling the mapper() function. This completes the process of mapping the Post model with its corresponding Table instance. We can now use Post model to query and manipulate the database.\r\n",
    "\r\n",
    "由于 ORM 构建在 Core 之上，SQLAlchemy 将使用模型定义创建 Table 对象，并通过调用 mapper() 函数将其与模型关联起来。这就完成了 Post 模型与其对应的 Table 实例的映射过程。我们现在可以使用 Post 模型来查询和操作数据库。\r\n",
    "\r\n",
    "We can peek at the Table instance associated with the model using the ```__table__``` attribute.\r\n",
    "\r\n",
    "我们可以使用 ```__table__``` 属性查看与模型关联的 Table 实例。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "Post.__table__"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Table('posts', MetaData(bind=None), Column('id', Integer(), table=<posts>, primary_key=True, nullable=False), Column('title', String(length=100), table=<posts>, nullable=False), Column('slug', String(length=100), table=<posts>, nullable=False), Column('content', String(length=50), table=<posts>, nullable=False), Column('published', String(length=200), table=<posts>, nullable=False), Column('created_on', DateTime(), table=<posts>, default=ColumnDefault(<function datetime.now at 0x00000229D6C32C10>)), Column('updated_on', DateTime(), table=<posts>, onupdate=ColumnDefault(<function datetime.now at 0x00000229D6C32DC0>), default=ColumnDefault(<function datetime.now at 0x00000229D6C32D30>)), schema=None)"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Classical Mappings 经典映射\r\n",
    "\r\n",
    "After reading the above section you might get an impression that to use SQLAlchemy's ORM, you will have to rewrite your existing Table instances as Models. However, this is simply not true.\r\n",
    "\r\n",
    "在阅读了上面的部分之后，您可能会得到这样的印象: 要使用 SQLAlchemy 的 ORM，您必须将现有的 Table 实例重写为 model。然而，事实并非如此。\r\n",
    "\r\n",
    "It turns out that you can map any plain Python class to a Table instance using the mapper() function. Here is an example:\r\n",
    "\r\n",
    "事实证明，可以使用 mapper() 函数将任何普通的 Python 类映射到 Table 实例。下面是一个例子:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "from sqlalchemy import MetaData, Table, String, Column, Text, DateTime, Boolean\r\n",
    "from sqlalchemy.orm import mapper\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "post = Table('post', metadata, \r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('title', String(200), nullable=False),\r\n",
    "    Column('slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),\r\n",
    "    Column('published', Boolean(),  default=False),\r\n",
    "    Column('created_on', DateTime(), default=datetime.now),\r\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    ")\r\n",
    "\r\n",
    "#新定义一个空类\r\n",
    "class Post(object): \r\n",
    "    pass\r\n",
    "\r\n",
    "#  参数: 要映射到的类和 `Table`对象\r\n",
    "mapper(Post, post)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<Mapper at 0x229d6ab87f0; Post>"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The mapper() class takes two arguments: class to be mapped and the Table object.\r\n",
    "\r\n",
    "Mapper() 类接受两个参数: 要映射的类和 Table 对象。\r\n",
    "\r\n",
    "Just by doing this the Post class will have attributes that corresponds to the columns of the table. Thus, the Post class now has following attributes:\r\n",
    "\r\n",
    "通过这样做，Post 类将具有与表的列相对应的属性。因此，Post 类现在有以下属性:\r\n",
    "\r\n",
    "- `post.id`\r\n",
    "- `post.title`\r\n",
    "- `post.slug`\r\n",
    "- `post.content`\r\n",
    "- `post.published`\r\n",
    "- `post.created_on`\r\n",
    "- `post.updated_on`\r\n",
    "\r\n",
    "The code in the above listing is functionally equivalent to the ```Post``` model we defined earlier.\r\n",
    "上面清单中的代码在功能上等同于我们前面定义的 `Post` 模型。\r\n",
    "\r\n",
    "Now you should have a good idea of what ```declarative_base()``` is doing for us behind the scenes.\r\n",
    "\r\n",
    "现在您应该对 ```declarative_base()``` 在幕后为我们所做的工作有了很好的了解。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Adding Keys and Constraints 添加键和约束\r\n",
    "\r\n",
    "When using ORM, we add keys and constraints to model using the `__table_args__` attribute.\r\n",
    "\r\n",
    "在使用 ORM 时，我们使用 `__table_args__` 属性添加键和约束来建模。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, ForeignKeyConstraint, Index\r\n",
    "\r\n",
    "class User(Base):\r\n",
    "    __tablename__ = 'users'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    username = Column(String(100), nullable=False)\r\n",
    "    email = Column(String(100), nullable=False)    \r\n",
    "    password = Column(String(200), nullable=False)\r\n",
    "    \r\n",
    "    __table_args__ = (\r\n",
    "        # PrimaryKeyConstraint('id', name='user_pk'),\r\n",
    "        UniqueConstraint('username'),\r\n",
    "        UniqueConstraint('email'),\r\n",
    "        {'extend_existing': True}\r\n",
    "    )\r\n",
    "\r\n",
    "\r\n",
    "class Post(Base):\r\n",
    "    __tablename__ = 'posts'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    slug = Column(String(100), nullable=False)\r\n",
    "    content = Column(String(50), nullable=False)\r\n",
    "    published = Column(String(200), nullable=False, default=False)\r\n",
    "    user_id = Column(Integer(), nullable=False)\r\n",
    "    created_on = Column(DateTime(), default=datetime.now)\r\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    "    \r\n",
    "    __table_args__ = (\r\n",
    "        ForeignKeyConstraint(['user_id'], ['users.id']),        \r\n",
    "        Index('title_content_index' 'title', 'content'), # composite index on title and content  \r\n",
    "        {'extend_existing': True}\r\n",
    "    )"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# efining Relationships 定义关系\r\n",
    "\r\n",
    "One to Many 一对多\r\n",
    "\r\n",
    "We create a one-to-many relationship by placing a foreign key on the child class. For example:\r\n",
    "\r\n",
    "我们通过在子类上放置一个外键来创建一对多关系。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "source": [
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    books = relationship(\"Book\")\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    author_id = Column(Integer, ForeignKey('authors.id'))\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "E:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\ext\\declarative\\clsregistry.py:125: SAWarning: This declarative base already contains a class with the same class name and module name as __main__.Author, and will be replaced in the string-lookup table.\n",
      "  util.warn(\n",
      "E:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\ext\\declarative\\clsregistry.py:125: SAWarning: This declarative base already contains a class with the same class name and module name as __main__.Book, and will be replaced in the string-lookup table.\n",
      "  util.warn(\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The line `author_id = Column(Integer, ForeignKey('authors.id')) `establishes a one-to-many relationship between the Author and Book model.\r\n",
    "\r\n",
    "这行`author_id = Column(Integer, ForeignKey('authors.id')) `: 在 Author 和 Book 模型之间建立一对多的关系。\r\n",
    "\r\n",
    "The `relationship()` function adds attributes on the models to access the related data. At its minimum, it accepts the name of the class representing the target of the relationship.\r\n",
    "\r\n",
    "`relationship()` 函数在模型上添加属性以访问相关数据。最低限度, 它需要传入表示关系目标的类的名称。\r\n",
    "\r\n",
    "The line `books = relationship(\"Book\")` adds a books attribute to the Author class.\r\n",
    "\r\n",
    "这行`books = relationship(\"Book\")` 向 Author 类添加了一个 books 属性。\r\n",
    "\r\n",
    "Given an `Author` object a, we can now access books written by him using a.books.\r\n",
    "\r\n",
    "给定一个`Author`对象 a，我们现在就可以使用 a.books 访问他写的书。\r\n",
    "\r\n",
    "What if we want to access book's author from a Book object?\r\n",
    "\r\n",
    "如果我们想从 Book 对象访问图书的作者，该怎么办？\r\n",
    "\r\n",
    "One way to achieve this is to define separate relationship() on Author model like this:\r\n",
    "\r\n",
    "实现这一点的一种方法是在 Author 模型上定义单独的`relationship()` ，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    books = relationship(\"Book\")\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    author_id = Column(Integer, ForeignKey('authors.id'))\r\n",
    "    author = relationship(\"Author\")  #  在 Author 模型上定义单独的`relationship()`\r\n",
    "    __table_args__ = {'extend_existing': True}"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Given a `Book`  object b, we can now access its author as b.author.\r\n",
    "\r\n",
    "给定一个 `Book` 对象 b，我们现在可以通过 b.author 访问其作者。\r\n",
    "\r\n",
    "Alternatively, we can use the `backref` parameters to specify the attribute name to be added on the other side of the relationship.\r\n",
    "\r\n",
    "另外，我们可以使用 `backref` 参数来指定要添加到关系的另一端的属性名。\r\n",
    "\r\n",
    "`books = relationship(\"Book\", backref=\"book\")`"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    books = relationship(\"Book\", backref=\"book\")  # 使用 `backref` 参数来指定要添加到关系的另一端的属性名 在Book 上就不用再加一个 relationship 了\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    author_id = Column(Integer, ForeignKey('authors.id'))\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Further, you can define `relationship()` on any side of the relationship. Thus, the preceding code can also be written as:\r\n",
    "\r\n",
    "此外，您可以在关系的任何一边定义 `relationship()`:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "source": [
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)    \r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    author_id = Column(Integer, ForeignKey('authors.id'))    \r\n",
    "    author = relationship(\"Author\", backref=\"books\")  # 换成在 这边定义关系了.\r\n",
    "    __table_args__ = {'extend_existing': True}"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# One to One 一对一\r\n",
    "\r\n",
    "Establishing a one-to-one relationship in SQLAlchemy is almost the same as one-to-many relationship, the only difference is that we pass an additional argument uselist=False to the relationship() function. Here is an example:\r\n",
    "\r\n",
    "在 SQLAlchemy 中建立一对一关系几乎与一对多关系相同，唯一的区别是我们向 `relationship()` 函数传递了一个额外的参数 `uselist=False`。下面是一个例子:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "source": [
    "from sqlalchemy import Date,DateTime\r\n",
    "\r\n",
    "class Person(Base):\r\n",
    "    __tablename__ = 'persons'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    name = Column(String(255), nullable=False)\r\n",
    "    designation = Column(String(255), nullable=False) \r\n",
    "    doj = Column(Date(), nullable=False)\r\n",
    "    dl = relationship('DriverLicense', backref='person', uselist=False)  # 关注这一行\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class DriverLicense(Base):\r\n",
    "    __tablename__ = 'driverlicense'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    license_number = Column(String(255), nullable=False)\r\n",
    "    renewed_on = Column(Date(), nullable=False)\r\n",
    "    expiry_date = Column(Date(), nullable=False)\r\n",
    "    person_id = Column(Integer(), ForeignKey('persons.id'))  # Foreign key\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Given a Person object p, p.dl would return a DriverLicense object. If we hadn't passed uselist=False to the relationship() function then the relationship between Person and DriverLicense would be one-to-many and p.dl would return a list of DriverLicense objects rather than a single object. The uselist=False argument doesn't have any effect on the persons attribute of the DriverLicense object. As usual, it will return a Person object.\r\n",
    "\r\n",
    "给定一个 `Person` 对象 p，p.dl 将返回一个 `DriverLicense` 对象。如果我们没有将 `uselist = False` 传递给 `relationship()` 函数，那么 `Person` 和 `DriverLicense` 之间的关系将是一对多的，而 p.dl 将返回一个 `DriverLicense` 对象列表，而不是单个对象。`Uselist = False` 参数对 `DriverLicense` 对象的 `persons` 属性没有任何影响。像往常一样，它将返回一个 `Person` 对象。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Many to Many 多对多\r\n",
    "\r\n",
    "Creating a many-to-many relationship requires an extra table called an association table or an intermediary table. We define this table as an instance of the `Table` class and then connect it to the model using the `secondary` argument of the `relationship()` function.\r\n",
    "\r\n",
    "创建多对多关系需要一个称为关联表或中介表的额外表。我们将这个表定义为 `Table` 类的一个实例，然后使用 `relationship()`函数的 `secondary`(次要的) 参数将其连接到模型。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "source": [
    "Base = declarative_base()\r\n",
    "\r\n",
    "# 关注这个表\r\n",
    "author_book = Table('author_book', Base.metadata,                   \r\n",
    "    Column('author_id', Integer(), ForeignKey(\"authors.id\")),\r\n",
    "    Column('book_id', Integer(), ForeignKey(\"books.id\"))\r\n",
    ")\r\n",
    "\r\n",
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'books'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    author_id = Column(Integer, ForeignKey('authors.id'))\r\n",
    "    author = relationship(\"Author\", secondary=author_book, backref=\"books\")   # 关注这行\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "An author can write one or more books. Similarly, a book can be written by multiple authors. Thus, there is a many-to-many relationship between author and book.\r\n",
    "\r\n",
    "一个作者可以写一本或多本书。同样，一本书可以由多个作者写成。因此，作者与书籍之间存在着`多对多`的关系。\r\n",
    "\r\n",
    "To represent many-to-many relationship we have created an association table called `author_book`.\r\n",
    "\r\n",
    "为了表示多对多关系，我们创建了一个名为 `author_book` 的关联表。\r\n",
    "\r\n",
    "Given an Author object a, we can access all books written by him as `a.books`. Similarly, given a Book object b, b.authors will return a list of Author objects.\r\n",
    "\r\n",
    "给定一个 `Author` 对象 a，我们可以通过`a.books`访问他所有的书  。类似地，给定一个 `Book` 对象 b，`b.authors` 将返回一个 `Author` 对象列表(可能多个作者)。\r\n",
    "\r\n",
    "In this case, we have defined relationship() on the Book model, but we could have just as easily defined it in the Author model.\r\n",
    "\r\n",
    "在这种情况下，我们已经在 `Book` 模型上定义了 `relationship()` ，但是我们也可以在 `Author` 模型中同样容易地定义它。\r\n",
    "\r\n",
    "There will be times when you want to store additional data in the association table. To achieve this we have to define association table as a model class.\r\n",
    "\r\n",
    "有时候您想要在关联表中存储额外的数据。要实现这一点，我们必须将关联表定义为一个模型类。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "source": [
    "Base = declarative_base()\r\n",
    "\r\n",
    "# 原代码报错: could not assemble any primary key columns for mapped table \r\n",
    "# 需要给 author_id, book_id 都加上 primary_key=True\r\n",
    "# 或者额外定义一个 id = Column(Integer, primary_key=True)\r\n",
    "# https://docs.sqlalchemy.org/en/14/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key\r\n",
    "\r\n",
    "class Author_Book(Base):\r\n",
    "    __tablename__ = 'author_book'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    author_id =  Column(Integer(), ForeignKey(\"authors.id\")) # , primary_key=True\r\n",
    "    book_id =  Column(Integer(), ForeignKey(\"books.id\")) # , primary_key=True\r\n",
    "    extra_data = Column(String(100))  # 存储额外的数据\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Author(Base):\r\n",
    "    __tablename__ = 'authors'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    books = relationship(\"Author_Book\", backref='author')\r\n",
    "    __table_args__ = {'extend_existing': True}\r\n",
    "\r\n",
    "class Book(Base):\r\n",
    "    __tablename__ = 'books'\r\n",
    "    id = Column(Integer, primary_key=True)\r\n",
    "    title = Column(String(100), nullable=False)\r\n",
    "    copyright = Column(SmallInteger, nullable=False)\r\n",
    "    authors = relationship(\"Author_Book\", backref=\"book\")\r\n",
    "    __table_args__ = {'extend_existing': True}"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "补充: How do I map a table that has no primary key? 如何映射没有主键的表？\r\n",
    "https://www.cnpython.com/qa/55240\r\n",
    "https://docs.sqlalchemy.org/en/14/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key\r\n",
    "\r\n",
    "The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well. These columns do not need to be actually known to the database as primary key columns, though it’s a good idea that they are. It’s only necessary that the columns behave as a primary key does, e.g. as a unique and not nullable identifier for a row.\r\n",
    "\r\n",
    "SQLAlchemy ORM 为了映射到特定的表，需要至少有一列表示为主键列; 多列(即复合主键)当然也是完全可行的。这些列实际上不需要被数据库知道为主键列，尽管它们是一个好主意。只有列的行为必须像主键一样，例如作为行的唯一标识符而不是空标识符。\r\n",
    "\r\n",
    "Most ORMs require that objects have some kind of primary key defined because the object in memory must correspond to a uniquely identifiable row in the database table; at the very least, this allows the object can be targeted for UPDATE and DELETE statements which will affect only that object’s row and no other. However, the importance of the primary key goes far beyond that. In SQLAlchemy, all ORM-mapped objects are at all times linked uniquely within a Session to their specific database row using a pattern called the identity map, a pattern that’s central to the unit of work system employed by SQLAlchemy, and is also key to the most common (and not-so-common) patterns of ORM usage.\r\n",
    "\r\n",
    "大多数 orm 要求对象具有某种定义的主键，因为内存中的对象必须对应于数据库表中唯一可识别的行; 至少，这允许对象成为 UPDATE 和 DELETE 语句的目标，这将只影响对象的行，而不影响其他。然而，主键的重要性远不止于此。在 SQLAlchemy 中，所有 ORM 映射的对象在任何时候都使用一个称为标识映射(identity map)的模式惟一地链接到它们特定的数据库行，这个模式是 SQLAlchemy 使用的工作系统单元的核心，也是 ORM 使用的最常见(和不常见)模式的关键。\r\n",
    "\r\n",
    "All tables in a relational database should have primary keys. Even a many-to-many association table - the primary key would be the composite of the two association columns:\r\n",
    "\r\n",
    "关系数据库中的所有表都应该有主键。即使是多对多关联表——主键也是两个关联列的组合:\r\n",
    "\r\n",
    "```\r\n",
    "CREATE TABLE my_association (\r\n",
    "  user_id INTEGER REFERENCES user(id),\r\n",
    "  account_id INTEGER REFERENCES account(id),\r\n",
    "  PRIMARY KEY (user_id, account_id)\r\n",
    ")\r\n",
    "```\r\n",
    "\r\n",
    "```\r\n",
    "class SomeClass(Base):\r\n",
    "    __table__ = some_table_with_no_pk\r\n",
    "    __mapper_args__ = {\r\n",
    "        'primary_key':[some_table_with_no_pk.c.uid, some_table_with_no_pk.c.bar]\r\n",
    "    }\r\n",
    "```    "
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Creating Tables 创建表格\r\n",
    "\r\n",
    "As in SQLAlchemy Core, use use `create_all()` method of the MetaData instance to create the table.\r\n",
    "与 SQLAlchemy Core 中一样，使用 `MetaData` 实例的 `create_all()`方法创建表。\r\n",
    "\r\n",
    "`Base.metadata.create_all(engine)`\r\n",
    "\r\n",
    "To drop the tables call drop_all method.\r\n",
    "\r\n",
    "若要删除表，请调用 drop _ all 方法。\r\n",
    "\r\n",
    "`Base.metadata.drop_all(engine)`\r\n",
    "\r\n",
    "We will now re-define the tables using models and persists them to the database by calling the create_all() method. Here is the complete code to do so:\r\n",
    "\r\n",
    "现在，我们将使用模型重新定义表，并通过调用 create _ all ()方法将它们持久化到数据库中。下面是完整的代码:\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric, SmallInteger\r\n",
    "\r\n",
    "from sqlalchemy.ext.declarative import declarative_base\r\n",
    "from sqlalchemy.orm import relationship\r\n",
    "\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "engine = create_engine(\"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts\")\r\n",
    "\r\n",
    "Base = declarative_base()\r\n",
    "\r\n",
    "class Customer(Base):\r\n",
    "    __tablename__ = 'customers'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    first_name = Column(String(100), nullable=False)\r\n",
    "    last_name = Column(String(100), nullable=False)\r\n",
    "    username = Column(String(50), nullable=False)\r\n",
    "    email = Column(String(200), nullable=False)\r\n",
    "    created_on = Column(DateTime(), default=datetime.now)\r\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    "    orders = relationship(\"Order\", backref='customer')\r\n",
    "\r\n",
    "\r\n",
    "class Item(Base):\r\n",
    "    __tablename__ = 'items'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    name = Column(String(200), nullable=False)\r\n",
    "    cost_price =  Column(Numeric(10, 2), nullable=False)\r\n",
    "    selling_price = Column(Numeric(10, 2),  nullable=False)\r\n",
    "#     orders = relationship(\"Order\", backref='customer')\r\n",
    "    \r\n",
    "\r\n",
    "class Order(Base):\r\n",
    "    __tablename__ = 'orders'\r\n",
    "    id = Column(Integer(), primary_key=True)\r\n",
    "    customer_id = Column(Integer(), ForeignKey('customers.id'))\r\n",
    "    date_placed = Column(DateTime(), default=datetime.now)\r\n",
    "    line_items = relationship(\"OrderLine\", secondary=\"order_lines\", backref='order')\r\n",
    "    \r\n",
    "\r\n",
    "class OrderLine(Base):\r\n",
    "    __tablename__ = 'order_lines'\r\n",
    "    id =  Column(Integer(), primary_key=True)\r\n",
    "    order_id = Column(Integer(), ForeignKey('orders.id'))\r\n",
    "    item_id = Column(Integer(), ForeignKey('items.id'))\r\n",
    "    quantity = Column(SmallInteger())\r\n",
    "    item = relationship(\"Item\")\r\n",
    "\r\n",
    "\r\n",
    "Base.metadata.create_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "注: 上面定义的关系貌似有BUG."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "In the next chapter, we will learn how to work with the database using SQLAlchemy ORM.\r\n",
    "\r\n",
    "在下一章中，我们将学习如何使用 SQLAlchemy ORM 处理数据库。\r\n",
    "\r\n",
    "[粗翻]  2021-08-24 Fully jupyter notebook and Chinese Verion CRAFTED BY YULK "
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}